package sql;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import sql.record.Course;
import sql.record.CourseSchedule;
import sql.record.Registration;
import sql.record.TableRecord;
import sql.record.User;

/**
 * Testing the following functionality:
 * <ul>
 * 	<li>Insertion</li>
 * 	<li>Searching for a record</li>
 * 	<li>Removing a record</li>
 * </ul>
 * @author Ron Galay
 *
 */
public class DBManagmentTest {

	User toInsert = null;
	Course nihul = null;
	CourseSchedule hours = null;
	
	@Before
	public void init() throws ClassNotFoundException, SQLException {
		
		toInsert = new User();
		toInsert.givenName = "ron";
		toInsert.phoneNumber = "0123";
		toInsert.surName = "galay";
		toInsert.type = 0;
		toInsert.userName = "1";
		
		DBManagment.createDB();
		DBManagment.createTable(toInsert);
		List<TableRecord> toInsertList = new LinkedList<TableRecord>();
		toInsertList.add(toInsert);
		DBManagment.insertRecord(toInsertList);
		
		// creating a course
		nihul = new Course();
		nihul.courseName = "nihul";
		nihul.capacity = 2;
		nihul.creditPoints = 3;
		nihul.description = "great course";
		DBManagment.createTable(nihul);
		
		// creating it's hours
		hours = new CourseSchedule();
		hours.courseName = "nihul";
		hours.day = 1; // sunday
		hours.endHour = 12;
		hours.startHour = 8;
		hours.groupNum = 3;
		DBManagment.createTable(hours);
		
		// adding to the db's
		List<TableRecord> records = new LinkedList<TableRecord>();
		records.add(nihul);
		records.add(hours);
		DBManagment.insertRecord(records);
	}
	
	@After
	public void clean() throws ClassNotFoundException, SQLException {
		
		DBManagment.destroyTable(toInsert);
		DBManagment.destroyTable(nihul);
		DBManagment.destroyTable(hours);
	}
	
	@Test
	public void testBasicRecordInsertion() throws 	ClassNotFoundException, 
													SQLException {
		
		// creating a new user to search by
		User search = new User();
		search.userName = "1";
		assertNotSame(search.givenName, "ron");
		
		List<User> searchRes = DBManagment.getRecord(search);
		assertEquals(1, searchRes.size());
		search = searchRes.get(0);
		assertTrue("ron".equals(search.givenName));
		assertTrue("0123".equals(search.phoneNumber));
	}
	
	@Test(expected= SQLException.class)
	public void testDuplicate() throws ClassNotFoundException, SQLException {
		
		// creating a duplicate record to the one we added (in before method)
		User search = new User();
		search.givenName = "artem";
		search.phoneNumber = "0123";
		search.surName = "mer";
		search.type = 0;
		search.userName = "1";
		
		// inserting it - we should receive an error
		List<TableRecord> toInsertList = new LinkedList<TableRecord>();
		toInsertList.add(search);
		DBManagment.insertRecord(toInsertList);
	}
	
	@Test
	public void testAlmostDuplicate() throws ClassNotFoundException, SQLException {
		
		// creating an almost duplicate record to the one we added (in before method)
		User search = new User();
		search.givenName = "ron";
		search.phoneNumber = "0123";
		search.surName = "galay";
		search.type = 0;
		search.userName = "2";
		
		// inserting it - we should receive an error
		List<TableRecord> toInsertList = new LinkedList<TableRecord>();
		toInsertList.add(search);
		DBManagment.insertRecord(toInsertList);
		
	}
	
	private void testAtomicCourseAux(int amount) throws ClassNotFoundException, SQLException {
		
		// creating a course
		Course nihul = new Course();
		nihul.courseName = "nihul";
		
		// creating it's hours
		CourseSchedule hours = new CourseSchedule();
		hours.courseName = "nihul";
		
		// searching for nihul
		List<Course> courses = DBManagment.getRecord(nihul);
		assertEquals(amount, courses.size());
		
		// searching for hours
		List<CourseSchedule> hours_ = DBManagment.getRecord(hours);
		assertEquals(amount, hours_.size());
	}
	
	@Test
	public void testAtomicInsertionCourse() throws ClassNotFoundException, SQLException {
		
		// testing that it was correctly added
		testAtomicCourseAux(1);
	}
	
	@Test 
	public void testUpdate() throws ClassNotFoundException, SQLException {
		
		// creating a new updated user
		User toBeUpdated = new User();
		toBeUpdated.givenName = "ron";
		toBeUpdated.phoneNumber = "0123";
		toBeUpdated.surName = "galay";
		toBeUpdated.type = 1;
		toBeUpdated.userName = "1";
		
		List<TableRecord> records = new LinkedList<TableRecord>();
		records.add(toBeUpdated);
		DBManagment.updateRecord(records);
		
		// checking that the value changed 
		List<User> updated = DBManagment.getRecord(toBeUpdated);
		assertTrue(updated.get(0).type == 1);
	}
	
	@Test
	public void testSearchNotInDB() throws ClassNotFoundException, SQLException {
		
		// creating a record that isn't in the table
		User nonExisting = new User();
		nonExisting.userName = "not";
		
		// no values should be found
		List<User> searchRes = DBManagment.getRecord(nonExisting);
		assertEquals(0, searchRes.size());
	}
	
	@Test
	public void testGetTable() throws ClassNotFoundException, SQLException {
		
		// inserting another user to the users db
		User additional = new User();
		additional.givenName = "artem";
		additional.phoneNumber = "01234";
		additional.surName = "merkovitch";
		additional.type = 0; // :)
		additional.userName = "2";
		
		List<TableRecord> records = new LinkedList<TableRecord>();
		records.add(additional);
		DBManagment.insertRecord(records);
		
		User tableSearch = new User();
		List<User> table = DBManagment.getTable(tableSearch);
		assertTrue(table.get(0).givenName.equals("ron"));
		assertTrue(table.get(1).givenName.equals("artem"));
	}
	
	@Test(expected=SQLException.class)
	public void testRegistration() throws ClassNotFoundException, SQLException {
		
		// register full amount of users to the course nihul
		List<TableRecord> add = new LinkedList<TableRecord>();
		DBManagment.destroyTable(new Registration());
		DBManagment.createTable(new Registration());

		Registration reg = new Registration("courseName");
		reg.courseName = "nihul";
		reg.userName = "1";
		add.add(reg);
		DBManagment.insertRecord(add);
		
		// try to register to the course
		Registration reg2 = new Registration("courseName");
		reg2.courseName = "nihul";
		reg2.userName = "2";
		
		Registration reg3 = new Registration("courseName");
		reg3.courseName = "nihul";
		reg3.userName = "3";
		
		add.clear();
		add.add(reg2);
		add.add(reg3);
		DBManagment.insertRecord(add);
		DBManagment.destroyTable(new Registration());
	}
	
	@Test
	public void testGetCoursesData() throws ClassNotFoundException, SQLException {
		
		// getting info on nihul classes
		List<TableRecord> courses = DBManagment.getCourseDetails();
		Course course = (Course) courses.get(0);
		assertTrue(course.courseName.equals("nihul"));
		CourseSchedule courseSchedule = (CourseSchedule) courses.get(1);
		assertTrue(courseSchedule.courseName.equals("nihul"));
	}
	
	@Test
	public void testGetMyCourse() throws ClassNotFoundException, SQLException {
		
		// registering to a course
		List<TableRecord> add = new LinkedList<TableRecord>();

		// registering our user to course nihul
		Registration reg = new Registration("courseName");
		reg.courseName = "nihul";
		reg.userName = "1";
		add.add(reg);
		DBManagment.destroyTable(reg);
		DBManagment.createTable(new Registration());
		DBManagment.insertRecord(add);
		
		// inserting another course
		Course noOverlap = new Course();
		noOverlap = new Course();
		noOverlap.courseName = "nihul2";
		noOverlap.capacity = 1;
		noOverlap.creditPoints = 3;
		noOverlap.description = "great course";
		CourseSchedule scheduleNoOverlap = new CourseSchedule();
		scheduleNoOverlap.courseName = "nihul2";
		scheduleNoOverlap.day = 1; // sunday
		scheduleNoOverlap.endHour = 13;
		scheduleNoOverlap.startHour = 10;
		scheduleNoOverlap.groupNum = 4;
		add.clear();
		add.add(noOverlap);
		add.add(scheduleNoOverlap);
		DBManagment.insertRecord(add);
		
		// getting all the courses that we are registered to
		List<TableRecord> res = DBManagment.getMyCourses(toInsert.userName);
		assertEquals(2, res.size());
		DBManagment.destroyTable(reg);
		
	}
	
	@Test
	public void testGetAvailableCourses() throws ClassNotFoundException, SQLException {
		
		List<TableRecord> add = new LinkedList<TableRecord>();

		// registering our user to course nihul
		Registration reg = new Registration("courseName");
		reg.courseName = "nihul";
		reg.userName = "1";
		add.add(reg);
		DBManagment.destroyTable(reg);
		DBManagment.createTable(new Registration());
		DBManagment.insertRecord(add);
		
		// getting all the courses that don't overlap for our user
		List<TableRecord> res = DBManagment.getAvailableCourses(toInsert);
		assertEquals(0, res.size());
		
		// adding another course that does overlap
		Course noOverlap = new Course();
		noOverlap = new Course();
		noOverlap.courseName = "nihul2";
		noOverlap.capacity = 1;
		noOverlap.creditPoints = 3;
		noOverlap.description = "great course";
		CourseSchedule scheduleNoOverlap = new CourseSchedule();
		scheduleNoOverlap.courseName = "nihul2";
		scheduleNoOverlap.day = 1; // sunday
		scheduleNoOverlap.endHour = 13;
		scheduleNoOverlap.startHour = 10;
		scheduleNoOverlap.groupNum = 4;
		add.clear();
		add.add(noOverlap);
		add.add(scheduleNoOverlap);
		DBManagment.insertRecord(add);
		
		// should get it in the query
		res = DBManagment.getAvailableCourses(toInsert);
		assertEquals(0, res.size());
	}
	
	@Test
	public void testBasicRemove() throws 	ClassNotFoundException,
											SQLException {
		
		// creating a new user to search by
		User search = new User();
		search.userName = "1";
		
		// removing the user from the table
		List<TableRecord> toRemoveList = new LinkedList<TableRecord>();
		toRemoveList.add(search);
		DBManagment.removeRecord(toRemoveList);
		
		// searching for it - shouldn't be found
		User searchDeleted =new User();
		searchDeleted.userName = "1";
		List<User> searchRes = DBManagment.getRecord(searchDeleted);
		
		assertEquals(0,searchRes.size());
	}
	
	@Test
	public void testRemoveNotInDB() throws ClassNotFoundException, SQLException {
		
		// creating a user to search by - shouldn't be in the db
		User search = new User();
		search.userName = "2";
		
		// trying to remove the user
		List<TableRecord> toRemoveList = new LinkedList<TableRecord>();
		toRemoveList.add(search);
		DBManagment.removeRecord(toRemoveList);
	}
	
	@Test
	public void testAtomicRemoveCourse() throws ClassNotFoundException, SQLException {
		
		// remove the records
		List<TableRecord> records = new LinkedList<TableRecord>();
		records.add(hours);
		records.add(nihul);
		
		DBManagment.removeRecord(records);
		testAtomicCourseAux(0);
	}
	
	@Test(expected = SQLException.class)
	public void testCollisionInRegistration() throws ClassNotFoundException, SQLException {
		
		Course nihul2 = new Course();
		nihul2.courseName = "nihul2";
		nihul2.capacity = 4;
		nihul2.creditPoints = 3;
		nihul2.description = "great course";
		CourseSchedule hours2 = new CourseSchedule();
		hours2.courseName = "nihul2";
		hours2.day = 1; // sunday
		hours2.endHour = 12;
		hours2.startHour = 8;
		hours2.groupNum = 4;
		List<TableRecord> add = new LinkedList<TableRecord>();
		add.add(nihul2);
		add.add(hours2);
		DBManagment.insertRecord(add);
		
		// adding a registration 
		Registration byUser1 = new Registration("userName");
		byUser1.userName = "1";
		byUser1.courseName = "nihul2";
		
		Registration byUser2= new Registration("userName");
		byUser2.userName = "1";
		byUser2.courseName = "nihul2";
		
		// creating the registration table and adding
		DBManagment.createTable(byUser1);
		add.clear();
		add.add(byUser2);
		add.add(byUser1);
		DBManagment.insertRecord(add);
	}
	
	@Test
	public void testRegistrationRemoval() throws SQLException, ClassNotFoundException {
		
		// adding course nihul2
		Course nihul2 = new Course();
		nihul2.courseName = "nihul2";
		nihul2.capacity = 4;
		nihul2.creditPoints = 3;
		nihul2.description = "great course";
		CourseSchedule hours2 = new CourseSchedule();
		hours2.courseName = "nihul2";
		hours2.day = 2; // sunday
		hours2.endHour = 12;
		hours2.startHour = 8;
		hours2.groupNum = 4;
		List<TableRecord> add = new LinkedList<TableRecord>();
		add.add(nihul2);
		add.add(hours2);
		DBManagment.insertRecord(add);
		
		// adding a registration 
		Registration byUser1 = new Registration("userName");
		byUser1.userName = "1";
		byUser1.courseName = "nihul";
		
		Registration byUser2= new Registration("userName");
		byUser2.userName = "1";
		byUser2.courseName = "nihul2";
		
		// creating the registration table and adding
		DBManagment.createTable(byUser1);
		add.clear();
		add.add(byUser2);
		add.add(byUser1);
		DBManagment.insertRecord(add);
		
		// removing it by the specified search parameter
		List<TableRecord> remove = new LinkedList<TableRecord>();
		remove.add(byUser1);
		DBManagment.removeRecord(remove);
		
		// checking that it doesn't exist
		List<Registration> res = DBManagment.getTable(byUser1);
		assertTrue(res.size() == 0);
		
		// adding them again and trying to remove by course
		byUser1 = new Registration("courseName");
		byUser1.userName = "1";
		byUser1.courseName = "nihul";
		
		byUser2= new Registration("courseName");
		byUser2.userName = "2";
		byUser2.courseName = "nihul2";
		
		add = new LinkedList<TableRecord>();
		add.add(byUser2);
		add.add(byUser1);
		DBManagment.insertRecord(add);

		remove = new LinkedList<TableRecord>();
		remove.add(byUser1);
		DBManagment.removeRecord(remove);
		
		// checking that it doesn't exist
		res = DBManagment.getTable(byUser1);
		assertTrue(res.size() == 1);
		
	}
	
	@Test
	public void testRegister() throws ClassNotFoundException, SQLException {
		
		// adding another course
		Course nihul2 = new Course();
		nihul2.courseName = "nihul2";
		nihul2.capacity = 4;
		nihul2.creditPoints = 3;
		nihul2.description = "great course";
		CourseSchedule hours2 = new CourseSchedule();
		hours2.courseName = "nihul2";
		hours2.day = 1; // sunday
		hours2.endHour = 12;
		hours2.startHour = 8;
		hours2.groupNum = 4;
		List<TableRecord> add = new LinkedList<TableRecord>();
		add.add(nihul2);
		add.add(hours2);
		DBManagment.insertRecord(add);
		
		// adding a registration 
		Registration byUser1 = new Registration("userName");
		byUser1.userName = "1";
		byUser1.courseName = "nihul";
		
		DBManagment.destroyTable(new Registration());
		DBManagment.createTable(byUser1);
		add.clear();
		add.add(byUser1);
		DBManagment.insertRecord(add);
	}
	
}
